﻿using DigDes.DSchool.SUPS.DataAccess.Interface;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;


namespace DigDes.DSchool.SUPS.DataAccess.Database
{
    public class BaseDAC
    {
        private static readonly string ConnectionStringKey = "SUPSDB";
        
        int Pagesize = 5;

        public static List<SUPSData> GetBaseInfo()
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "SELECT TOP 20 * FROM SomeTable";
                    sqlConn.Open();

                    List<SUPSData> data = new List<SUPSData>();
                    SqlDataReader reader = sqlCmd.ExecuteReader();
                    while (reader.Read())
                    {
                        data.Add(GetRowFromDB(reader));
                    }
                    return data;
                }
            }

        }

        public static SUPSData GetRowFromDB(SqlDataReader reader){
            SUPSData row = new SUPSData();

            row.CarNumber = reader["CarNumber"].ToString();
            row.Weight = reader["Weight"].ToString();
            row.ArrivalStation = reader["ArrivalStation"].ToString();
            row.CargoCode = reader["CargoCode"].ToString();
            row.ConsigneeCode = reader["ConsigneeCode"].ToString();
            row.CarParkType = reader["CarParkType"].ToString();
            row.DispatchStation = reader["DispatchStation"].ToString();
            row.OperationCode = reader["OperationCode"].ToString();
            row.OperationDate = reader["OperationDate"].ToString();
            row.OperationYear = reader["OperationYear"].ToString();
            row.OperationTime = reader["OperationTime"].ToString();
            row.OperationStation = reader["OperationStation"].ToString();
            row.RoadOfDelivery = reader["RoadOfDelivery"].ToString();
            row.RoadOfReception = reader["RoadOfReception"].ToString();
            row.TrainIndex = reader["TrainIndex"].ToString();
            row.TrainNumber = reader["TrainNumber"].ToString();
            
            return row;       
        }

        public static List<SUPSData> Paging(int maximumRows, int startRowIndex)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure; 
                    sqlCmd.CommandText = "GetSUPSRows";
                    sqlCmd.Parameters.Add("@maximumRows", maximumRows);
                    sqlCmd.Parameters.Add("@startRowIndex", startRowIndex);
                    
                    sqlConn.Open();
                    SqlDataReader reader = sqlCmd.ExecuteReader();
                    List<SUPSData> info = new List<SUPSData>();
                    while (reader.Read())
                    {
                        info.Add(GetRowFromDB(reader));
                    }
                    return info;
                }
            }
        }

        public static int CountOfRows(int maximumRows, int startRowIndex)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;

            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = "SELECT COUNT(*) FROM SomeTable";

                    sqlConn.Open();
                    int count = System.Convert.ToInt32(sqlCmd.ExecuteScalar());
                    sqlConn.Close();
                    return count;
                }
            }
        }

        public static SUPSData GetSpecicalCar(string CarNum)
        {
            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCmd.CommandText = "GetSpecialCar";
                    sqlCmd.Parameters.Add("@CarNum", CarNum);


                    sqlConn.Open();
                    SqlDataReader reader = sqlCmd.ExecuteReader();
                    reader.Read();
                    return GetRowFromDB(reader);
                }
            }
        }

        public static SUPSData GetSUPSRow(string CarNum)
        {
             var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
             using (var sqlConn = new SqlConnection(connectionString))
             {
                 using (var sqlCmd = sqlConn.CreateCommand())
                 {
                     sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                     sqlCmd.CommandText = "GetSUPSRow";
                     sqlCmd.Parameters.Add("@CarNum", CarNum);
                     

                     sqlConn.Open();
                     SqlDataReader reader = sqlCmd.ExecuteReader();
                     reader.Read();
                     return GetRowFromDB(reader);
                 }
             }
        }

        public static void UpdateSUPS(string CarNumber, string Weight, string ArrivalStation, string CargoCode,
            string ConsigneeCode, string CarParkType, string DispatchStation, string OperationCode, string OperationDate,
            string OperationYear, string OperationTime, string OperationStation, string RoadOfDelivery, string RoadOfReception,
            string TrainIndex, string TrainNumber)
        {

            var connectionString = ConfigurationManager.ConnectionStrings[ConnectionStringKey].ConnectionString;
            using (var sqlConn = new SqlConnection(connectionString))
            {
                using (var sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    sqlCmd.CommandText = "UpdateSUPS";

                    sqlCmd.Parameters.Add("@CarNumber", CarNumber);
                    sqlCmd.Parameters.Add("@Weight", Weight);
                    sqlCmd.Parameters.Add("@ArrivalStation", ArrivalStation);
                    sqlCmd.Parameters.Add("@CargoCode", CargoCode);
                    sqlCmd.Parameters.Add("@ConsigneeCode", ConsigneeCode);
                    sqlCmd.Parameters.Add("@CarParkType", CarParkType);
                    sqlCmd.Parameters.Add("@DispatchStation", DispatchStation);
                    sqlCmd.Parameters.Add("@OperationCode", OperationCode);
                    sqlCmd.Parameters.Add("@OperationDate", OperationDate);
                    sqlCmd.Parameters.Add("@OperationYear", OperationYear);
                    sqlCmd.Parameters.Add("@OperationTime", OperationTime); 
                    sqlCmd.Parameters.Add("@OperationStation", OperationStation);
                    sqlCmd.Parameters.Add("@RoadOfDelivery", RoadOfDelivery);
                    sqlCmd.Parameters.Add("@RoadOfReception", RoadOfReception);
                    sqlCmd.Parameters.Add("@TrainIndex", TrainIndex);
                    sqlCmd.Parameters.Add("@TrainNumber", TrainNumber);

                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();
                }
            } 
        }            
    }
}
